package com.pokermind.dse.util;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;

/**
 * 数据库连接测试工具
 * 在应用启动时验证SQLite连接和表结构
 * 
 * @author PokerMind Team
 */
//@Component  // Temporarily disabled for quick startup
public class DatabaseConnectionTest implements CommandLineRunner {
    
    @Autowired
    private DataSource dataSource;
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Override
    public void run(String... args) throws Exception {
        testDatabaseConnection();
        verifyTableStructure();
        testBasicOperations();
    }
    
    /**
     * 测试数据库连接
     */
    private void testDatabaseConnection() {
        try (Connection connection = dataSource.getConnection()) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            System.out.println("=== SQLite Database Connection Test ===");
            System.out.println("Database URL: " + metaData.getURL());
            System.out.println("Database Product: " + metaData.getDatabaseProductName());
            System.out.println("Database Version: " + metaData.getDatabaseProductVersion());
            System.out.println("Driver Name: " + metaData.getDriverName());
            System.out.println("Driver Version: " + metaData.getDriverVersion());
            System.out.println("Connection successful! ✓");
            
        } catch (Exception e) {
            System.err.println("Database connection failed: " + e.getMessage());
            throw new RuntimeException("Database connection test failed", e);
        }
    }
    
    /**
     * 验证表结构
     */
    private void verifyTableStructure() {
        try {
            System.out.println("\n=== Table Structure Verification ===");
            
            // 检查所需的表是否存在
            String[] requiredTables = {
                "poker_users", 
                "poker_hands", 
                "poker_hand_actions", 
                "poker_decision_snapshots"
            };
            
            for (String tableName : requiredTables) {
                Integer count = jdbcTemplate.queryForObject(
                    "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name = ?",
                    Integer.class,
                    tableName
                );
                
                if (count != null && count > 0) {
                    System.out.println("Table " + tableName + " exists ✓");
                    
                    // 获取表的行数
                    Integer rowCount = jdbcTemplate.queryForObject(
                        "SELECT COUNT(*) FROM " + tableName,
                        Integer.class
                    );
                    System.out.println("  - Rows: " + rowCount);
                    
                } else {
                    System.out.println("Table " + tableName + " NOT FOUND ✗");
                    throw new RuntimeException("Required table " + tableName + " not found");
                }
            }
            
        } catch (Exception e) {
            System.err.println("Table structure verification failed: " + e.getMessage());
            throw new RuntimeException("Table verification failed", e);
        }
    }
    
    /**
     * 测试基本数据库操作
     */
    private void testBasicOperations() {
        try {
            System.out.println("\n=== Basic Operations Test ===");
            
            // 测试查询操作
            String sql = "SELECT COUNT(*) as total_users FROM poker_users";
            Integer userCount = jdbcTemplate.queryForObject(sql, Integer.class);
            System.out.println("Total users in database: " + userCount);
            
            // 测试当前时间
            sql = "SELECT datetime('now') as current_time";
            String currentTime = jdbcTemplate.queryForObject(sql, String.class);
            System.out.println("Database current time: " + currentTime);
            
            // 测试SQLite版本
            sql = "SELECT sqlite_version() as version";
            String version = jdbcTemplate.queryForObject(sql, String.class);
            System.out.println("SQLite version: " + version);
            
            // 测试PRAGMA设置
            sql = "PRAGMA foreign_keys";
            Integer foreignKeys = jdbcTemplate.queryForObject(sql, Integer.class);
            System.out.println("Foreign keys enabled: " + (foreignKeys == 1 ? "YES" : "NO"));
            
            System.out.println("Basic operations test completed ✓");
            
        } catch (Exception e) {
            System.err.println("Basic operations test failed: " + e.getMessage());
            throw new RuntimeException("Basic operations test failed", e);
        }
    }
}